<?php

namespace App\Console\Commands;

use App\Models\Book;
use App\Models\DailyBook;
use App\Models\Orders;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class DailyBookTotal extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'daily-book-total {--type= : 任务类型，例如 date 或 full}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每日统计--书本';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $today = date('Y-m-d');
        $type = $this->option('type') ?? 'full';//date 全部 或  指定日期

        //所有书籍
        $books = Book::all();

        /** @var \App\Models\Book $book */
        foreach($books as $book){
            $data = [
                'active_ct' => 0,//访问人数
                'new_ct' => 0,//新用户数 -- 定义待确认
                'auth_ct' => 0,//授权人数
                'pay_ct' => 0,//付费人数
                'pay_chapter_ct' => 0,//单章节购买人数
                'pay_all_chapter_ct' => 0,//剩余章节购买人数
                'member_ct' => 0,//大会员购买人数
                'income' => 0,//合计收入
            ];

            if ($type == 'full'){
                //访问人数
                $data['active_ct'] = DB::table(DB::raw('(select user_id from user_chapter_word where book_id = ? group by user_id) as a'))
                    ->setBindings([$book->id])
                    ->count();

                //授权人数
                $data['auth_ct'] = DB::table(DB::raw('(select id from users where auth_type = 0 and auth_target_id = ? ) as a'))
                    ->setBindings([$book->id])
                    ->count();

                //付费人数
                $data['pay_ct'] = DB::table(DB::raw('(select user_id from orders where book_id = ? and status = ? group by user_id) as a'))
                    ->setBindings([$book->id, Orders::STATUS_SUCCESS])
                    ->count();

                $buySql = '(select user_id from orders where book_id = ?  and status = ? and goods_type = ? group by user_id) as a';
                //单章节购买人数
                $data['pay_chapter_ct'] = DB::table(DB::raw($buySql))->setBindings([
                    $book->id,
                    Orders::STATUS_SUCCESS,
                    Orders::GOODS_TYPE_BOOK_CHAPTER
                ])->count();
                //剩余章节购买人数
                $data['pay_all_chapter_ct'] = DB::table(DB::raw($buySql))
                    ->setBindings([$book->id, Orders::STATUS_SUCCESS, Orders::GOODS_TYPE_BOOK])
                    ->count();
                //大会员购买人数
                $data['member_ct'] = DB::table(DB::raw($buySql))
                    ->setBindings([$book->id, Orders::STATUS_SUCCESS, Orders::GOODS_TYPE_MEMBER])
                    ->count();

                // //合计收入
                $data['income'] = DB::table(DB::raw('(select price from orders where book_id = ?  and status = ? ) as a'))->setBindings([
                        $book->id,
                        Orders::STATUS_SUCCESS
                    ])->sum('price');


                $book->fill($data);
                $book->save();

            }else{
                //日报表
                $dailyBook = DailyBook::firstOrCreate(['date' => $today, 'book_id' => $book->id]);

                //访问人数
                $data['active_ct'] = DB::table(DB::raw('(select user_id from user_chapter_word where book_id = ? and date = ? group by user_id) as a'))
                    ->setBindings([$book->id, $today])
                    ->count();

                //授权人数
                $data['auth_ct'] = DB::table(DB::raw('(select id from users where auth_type = 0 and auth_target_id = ? and mobile_time >= ?) as a'))
                    ->setBindings([$book->id, strtotime($today)])
                    ->count();


                //付费人数
                $data['pay_ct'] = DB::table(DB::raw('(select user_id from orders where book_id = ? and date = ? and status = ? group by user_id) as a'))
                    ->setBindings([$book->id, $today, Orders::STATUS_SUCCESS])
                    ->count();

                $buySql = '(select user_id from orders where book_id = ? and date = ? and status = ? and goods_type = ? group by user_id) as a';
                //单章节购买人数
                $data['pay_chapter_ct'] = DB::table(DB::raw($buySql))->setBindings([
                    $book->id,
                    $today,
                    Orders::STATUS_SUCCESS,
                    Orders::GOODS_TYPE_BOOK_CHAPTER
                ])->count();

                //剩余章节购买人数
                $data['pay_all_chapter_ct'] = DB::table(DB::raw($buySql))->setBindings([
                    $book->id,
                    $today,
                    Orders::STATUS_SUCCESS,
                    Orders::GOODS_TYPE_BOOK
                ])->count();

                //大会员购买人数
                $data['member_ct'] = DB::table(DB::raw($buySql))
                    ->setBindings([$book->id, $today, Orders::STATUS_SUCCESS, Orders::GOODS_TYPE_MEMBER])
                    ->count();

                // //合计收入
                $data['income'] = DB::table(DB::raw('(select price from orders where book_id = ? and date = ? and status = ? ) as a'))
                    ->setBindings([$book->id, $today, Orders::STATUS_SUCCESS])
                    ->sum('price');

                $dailyBook->fill($data);
                $dailyBook->save();
            }

        }
    }
}
